
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 10/29/2011 19:10:19
-- Generated from EDMX file: C:\Projects\SSOA\DataAccessService\SiteXtender.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [SiteXtender];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_LogLogLevel]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[LogSet] DROP CONSTRAINT [FK_LogLogLevel];
GO
IF OBJECT_ID(N'[dbo].[FK_MessageUser]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[MessageSet] DROP CONSTRAINT [FK_MessageUser];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[LogSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[LogSet];
GO
IF OBJECT_ID(N'[dbo].[LogLevelSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[LogLevelSet];
GO
IF OBJECT_ID(N'[dbo].[MessageSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[MessageSet];
GO
IF OBJECT_ID(N'[dbo].[UserSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[UserSet];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'LogSet'
CREATE TABLE [dbo].[LogSet] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [LoggerName] nvarchar(max)  NULL,
    [Message] nvarchar(max)  NULL,
    [ClientAddress] nvarchar(max)  NULL,
    [DateTime] datetime  NULL,
    [Guid] uniqueidentifier  NULL,
    [Level_Id] int  NOT NULL
);
GO

-- Creating table 'LogLevelSet'
CREATE TABLE [dbo].[LogLevelSet] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NULL
);
GO

-- Creating table 'MessageSet'
CREATE TABLE [dbo].[MessageSet] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Content] nvarchar(max)  NOT NULL,
    [DateTime] datetime  NOT NULL,
    [Uri] nvarchar(max)  NOT NULL,
    [User_Id] int  NULL
);
GO

-- Creating table 'UserSet'
CREATE TABLE [dbo].[UserSet] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Login] nvarchar(max)  NOT NULL,
    [Password] nvarchar(max)  NOT NULL,
    [FirstName] nvarchar(max)  NOT NULL,
    [LastName] nvarchar(max)  NOT NULL,
    [Email] nvarchar(max)  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [Id] in table 'LogSet'
ALTER TABLE [dbo].[LogSet]
ADD CONSTRAINT [PK_LogSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'LogLevelSet'
ALTER TABLE [dbo].[LogLevelSet]
ADD CONSTRAINT [PK_LogLevelSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'MessageSet'
ALTER TABLE [dbo].[MessageSet]
ADD CONSTRAINT [PK_MessageSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'UserSet'
ALTER TABLE [dbo].[UserSet]
ADD CONSTRAINT [PK_UserSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [Level_Id] in table 'LogSet'
ALTER TABLE [dbo].[LogSet]
ADD CONSTRAINT [FK_LogLogLevel]
    FOREIGN KEY ([Level_Id])
    REFERENCES [dbo].[LogLevelSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_LogLogLevel'
CREATE INDEX [IX_FK_LogLogLevel]
ON [dbo].[LogSet]
    ([Level_Id]);
GO

-- Creating foreign key on [User_Id] in table 'MessageSet'
ALTER TABLE [dbo].[MessageSet]
ADD CONSTRAINT [FK_MessageUser]
    FOREIGN KEY ([User_Id])
    REFERENCES [dbo].[UserSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_MessageUser'
CREATE INDEX [IX_FK_MessageUser]
ON [dbo].[MessageSet]
    ([User_Id]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------


DELETE FROM [SiteXtender].[dbo].[UserSet];
INSERT INTO [SiteXtender].[dbo].[UserSet]
           ([Login]
           ,[Password]
           ,[FirstName]
           ,[LastName]
           ,[Email])
     VALUES
           ('MaciejLisCK',
           'pass',
           'Maciej',
           'Lis',
           'mail@maciejlis.com')
GO

INSERT INTO [SiteXtender].[dbo].[UserSet]
           ([Login]
           ,[Password]
           ,[FirstName]
           ,[LastName]
           ,[Email])
     VALUES
           ('JanKowalski',
           'pass',
           'Jan',
           'Kowalski',
           'jankowalski@gmail.com')
GO

INSERT INTO [SiteXtender].[dbo].[UserSet]
           ([Login]
           ,[Password]
           ,[FirstName]
           ,[LastName]
           ,[Email])
     VALUES
           ('MarcinNowak',
           'pass',
           'Marcin',
           'Nowak',
           'marcinnowak@gmail.com')
GO

INSERT INTO [SiteXtender].[dbo].[UserSet]
           ([Login]
           ,[Password]
           ,[FirstName]
           ,[LastName]
           ,[Email])
     VALUES
           ('DarekLewandowski',
           'pass',
           'Darek',
           'Lewandowski',
           'dareklewandowski@gmail.com')
GO

DELETE FROM [SiteXtender].[dbo].[MessageSet];

INSERT INTO [SiteXtender].[dbo].[MessageSet]
           ([Content]
           ,[DateTime]
           ,[Uri]
           ,[User_Id])
     VALUES
           ('Hello World!',
           convert(datetime,'2011-09-21 12:45:21.000', 120),
           'http://maciejlis.com/',
           1)
GO


INSERT INTO [SiteXtender].[dbo].[MessageSet]
           ([Content]
           ,[DateTime]
           ,[Uri]
           ,[User_Id])
     VALUES
           ('Echo!',
           convert(datetime,'2011-09-21 12:45:21.000', 120),
           'http://maciejlis.com/',
           1)
GO


INSERT INTO [SiteXtender].[dbo].[MessageSet]
           ([Content]
           ,[DateTime]
           ,[Uri]
           ,[User_Id])
     VALUES
           ('Test string',
           convert(datetime,'2011-09-21 12:45:21.000', 120),
           'http://maciejlis.com/',
           2)
GO


INSERT INTO [SiteXtender].[dbo].[MessageSet]
           ([Content]
           ,[DateTime]
           ,[Uri]
           ,[User_Id])
     VALUES
           ('Foo',
           convert(datetime,'2011-09-21 12:45:21.000', 120),
           'http://maciejlis.com/',
           3)
GO


INSERT INTO [SiteXtender].[dbo].[MessageSet]
           ([Content]
           ,[DateTime]
           ,[Uri]
           ,[User_Id])
     VALUES
           ('Bar',
           convert(datetime,'2011-09-21 12:45:21.000', 120),
           'http://maciejlis.com/',
           1)
GO


INSERT INTO [SiteXtender].[dbo].[MessageSet]
           ([Content]
           ,[DateTime]
           ,[Uri]
           ,[User_Id])
     VALUES
           ('Baz',
           convert(datetime,'2011-09-21 12:45:21.000', 120),
           'http://maciejlis.com/',
           2)
GO


INSERT INTO [SiteXtender].[dbo].[MessageSet]
           ([Content]
           ,[DateTime]
           ,[Uri]
           ,[User_Id])
     VALUES
           ('Some example content',
           convert(datetime,'2011-09-21 12:45:21.000', 120),
           'http://maciejlis.com/',
           3)
GO


INSERT INTO [SiteXtender].[dbo].[MessageSet]
           ([Content]
           ,[DateTime]
           ,[Uri]
           ,[User_Id])
     VALUES
           ('Example string',
           convert(datetime,'2011-09-21 12:45:21.000', 120),
           'http://maciejlis.com/',
           3)
GO


